﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ProdeManagmente.Libs;
using ProdeManagmente.Model;
using System.Data;

namespace ProdeManagmente.DAO
{
    public class UserService
    {
        private static UserService instance;
        private static String TBUser = "UserProde";
        private static String TBUserTournament = "UserTournament";

        private UserService()
        { }

        public static UserService getUserServiceInstance()
        {
            if (instance == null)
            {
                instance = new UserService();
            }
            return instance;
        }

        public User getUserById(long id)
        {
            User user = null;

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBUser + " " +
                            "WHERE ID = " + id;

            DataTable userTable = conn.executeQuery(SQL);

            foreach (DataRow userRow in userTable.Rows)
            {
                user = new User(id,
                                userRow["code"].ToString(),
                                userRow["name"].ToString(),
                                userRow["mail"].ToString(),
                                userRow["password"].ToString());
            }

            if (user == null)
            {
                throw new BusinessException("301", "No se encontro el elemento solicidado");
            }
            return user;
        }

        public User getUserByCode(String code)
        {
            User user = null;
            try
            {
                Persistencia conn = Persistencia.getInstance();
                String SQL = "SELECT * FROM " + TBUser + " " +
                                "WHERE Code = '" + code + "' ";

                DataTable userTable = conn.executeQuery(SQL);

                foreach (DataRow userRow in userTable.Rows)
                {
                    user = new User(Convert.ToInt64(userRow["id"].ToString()),
                                    userRow["code"].ToString(),
                                    userRow["name"].ToString(),
                                    userRow["mail"].ToString(),
                                    userRow["password"].ToString());
                }

                if (user == null)
                {
                    throw new BusinessException("301", "No se encontro el elemento solicidado");
                }
            }
            catch (BusinessException be)
            {
                throw be;
            }
            catch (Exception e)
            {
                throw new BusinessException("310", e.Message);
            }
            return user;
        }

        /**
         * Obtiene la lista de usuarios, filtro SIN la palabra clave WHERE
         */
        public List<User> getUserListFilter(String filter)
        {
            List<User> userList = new List<User>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBUser + " ";

            if (!filter.Trim().Equals(""))
            {
                SQL = SQL + "WHERE " + filter;
            }

            DataTable userTable = conn.executeQuery(SQL);

            foreach (DataRow userRow in userTable.Rows)
            {
                User user = new User(Convert.ToInt64(userRow["id"].ToString()),
                                    userRow["code"].ToString(),
                                    userRow["name"].ToString(),
                                    userRow["mail"].ToString(),
                                    userRow["password"].ToString());
                userList.Add(user);
            }
            return userList;
        }

        /**
        * Obtiene la lista de torneos, filtro SIN la palabra clave WHERE
        */
        public List<Tournament> getTournametesByUser(long userId, String filter)
        {
            List<Tournament> tournamenteList = new List<Tournament>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBUserTournament + " " +
                         "WHERE idUser = " + userId + " ";

            if (!filter.Trim().Equals(""))
            {
                SQL = SQL + "AND " + filter;
            }

            DataTable tournamenteTable = conn.executeQuery(SQL);

            foreach (DataRow tournamenteRow in tournamenteTable.Rows)
            {
                TournamenteService tournamenteService = TournamenteService.getTournamenteServiceInstance();
                Tournament tounarmente = tournamenteService.getTournamentById(Convert.ToInt64(tournamenteRow["idTournament"]));
                tournamenteList.Add(tounarmente);
            }
            return tournamenteList;
        }

        public List<Tournament> getTournametesIdByUser(long userId, String filter)
        {
            List<Tournament> tournamenteList = new List<Tournament>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBUserTournament + " " +
                         "WHERE idUser = " + userId + " ";

            if (!filter.Trim().Equals(""))
            {
                SQL = SQL + "AND " + filter;
            }

            DataTable tournamenteTable = conn.executeQuery(SQL);

            foreach (DataRow tournamenteRow in tournamenteTable.Rows)
            {
                long idTournamente = Convert.ToInt64(tournamenteRow["idTournament"]);
                Tournament tounarmente = new Tournament(idTournamente, "", new DateTime(), new DateTime());
                tournamenteList.Add(tounarmente);
            }
            return tournamenteList;
        }

        /**
        * Obtiene la lista de usuarios, filtro SIN la palabra clave WHERE
        */
        public List<User> getUserByTournament(long tournamentId, String filter)
        {
            List<User> userList = new List<User>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBUserTournament + " " +
                         "WHERE idTournament = " + tournamentId + " ";

            if (!filter.Trim().Equals(""))
            {
                SQL = SQL + "AND " + filter;
            }

            DataTable tournamenteTable = conn.executeQuery(SQL);

            foreach (DataRow tournamenteRow in tournamenteTable.Rows)
            {
                User user = getUserById(Convert.ToInt64(tournamenteRow["idUser"]));
                userList.Add(user);
            }
            return userList;
        }

        public List<User> getUserIdByTournament(long tournamentId, String filter)
        {
            List<User> userList = new List<User>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBUserTournament + " " +
                         "WHERE idTournament = " + tournamentId + " ";

            if (!filter.Trim().Equals(""))
            {
                SQL = SQL + "AND " + filter;
            }

            DataTable tournamenteTable = conn.executeQuery(SQL);

            foreach (DataRow tournamenteRow in tournamenteTable.Rows)
            {
                long idUser = Convert.ToInt64(tournamenteRow["idUser"]);
                User user = new User(idUser, "", "", "", "");
                userList.Add(user);
            }
            return userList;
        }

        public bool isUserInTournament(long idUser, long idTournament)
        {
            List<User> userList = new List<User>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBUserTournament + " " +
                         "WHERE idTournament = " + idTournament + " AND " +
                         "idUser = " + idUser + " ";

            DataTable tournamenteTable = conn.executeQuery(SQL);

            foreach (DataRow tournamenteRow in tournamenteTable.Rows)
            {
                User user = getUserById(Convert.ToInt64(tournamenteRow["idUser"]));
                userList.Add(user);
            }
            return userList.Count > 0;
        }

        public User save(User user)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";
            if (user.getId() != 0) //Si es diferente de 0 es por que ya existe entonces tiene que actualizarlo
            {
                SQL = "UPDATE  " + TBUser + " SET " +
                      "code = '" + user.getCode() + "', " +
                      "name = '" + user.getName() + "', " +
                      "mail = '" + user.getMail() + "', " +
                      "password = '" + user.getPassword() + "' " +
                      "WHERE id = " + user.getId() + " ";

                if (user.getTournaments() != null)
                {
                    foreach (Tournament tounarmente in user.getTournaments())
                    {
                        saveUserTournament(user, tounarmente);
                    }
                }
            }
            else //Si es null es por que es un objeto nuevo
            {
                user.setId(getNewId());
                SQL = "INSERT INTO " + TBUser + " " +
                       "(id, code, name, mail, password) VALUES (" +
                       "" + user.getId() + ", " +
                       "'" + user.getCode() + "', " +
                       "'" + user.getName() + "', " +
                       "'" + user.getMail() + "', " +
                       "'" + user.getPassword() + "') ";
            }

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (result <= 0)
            {
                throw new BusinessException("302", "No se pudo grabar el elemento");
            }

            return user;
        }

        public void delete(long id)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";

            SQL = "DELETE  " + TBUser + "  " +
                    "WHERE id = " + id + " ";

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (result <= 0)
            {
                throw new BusinessException("303", "No se pudo borrar el elemento");
            }
        }

        public void saveUserTournament(User user, Tournament tournamente)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";
            SQL = "INSERT INTO " + TBUserTournament + " " +
                    "(idUser, idTournament) VALUES (" +
                    "" + user.getId() + ", " +
                    "" + tournamente.getId() + ") ";

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));

            if (result <= 0)
            {
                throw new BusinessException("302", "No se pudo grabar el elemento");
            }
        }

        private long getNewId()
        {
            long id = 0L;
            String strId;
            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT MAX(id) FROM " + TBUser + " ";

            DataTable userTable = conn.executeQuery(SQL);

            foreach (DataRow userRow in userTable.Rows)
            {
                strId = userRow[0].ToString();
                if (strId.Equals(""))
                {
                    strId = "0";
                }
                id = Convert.ToInt64(strId) + 1;
            }
            return id;
        }
    }
}
